package server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

import exceptions.CombinateDataException;

public class DataCombinator {
	private Connection conn;
	
	private void startDB() throws CombinateDataException{
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn=DriverManager.getConnection("jdbc:oracle:thin:@172.16.1.230:1521:tarena","scott","tiger");
		} catch (Exception e) {
			e.printStackTrace();
			throw new CombinateDataException(e);
		}
	}
	//day的格式"1970-01-01"
	public void combineDayData(String day)throws  CombinateDataException{
		try {
			startDB();
			conn.setAutoCommit(false);
			
			//得到创建日报表表名并建表，data20100120
			String[] daytables = day.split("-");
			String daytable = "heantai_day";
			for (String s : daytables) {
				daytable+=s;
			}
			String createTable="create table "+daytable+" (id char(20), labip char(15)," +
					" durations numeric(12) not null, loginmonth Date,primary key(id,labip))";
			PreparedStatement pst = conn.prepareStatement(createTable);
			pst.execute();
			
			//
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			Date d =sdf.parse(day);
			long daystart=d.getTime();
			long dayend = daystart+24*60*60*1000-1;
			String query = "select logname,labip,sum(durations) DAY_TIME from heantai_datamingxi " +
					"where logouttime between ? and ? group by logname,labip";
			pst = conn.prepareStatement(query);
			pst.setLong(1, daystart);
			pst.setLong(2,dayend);
			ResultSet rs = pst.executeQuery();
			rs.setFetchSize(10);
			
			//
			sdf = new SimpleDateFormat("yyyy-MM");
			String month = day.substring(0,day.lastIndexOf("-"));
			Date mon = sdf.parse(month);
			java.sql.Date m = new java.sql.Date(mon.getTime());//m->loginmonth
			String update = "insert into "+daytable+" (id,labip,durations,loginmonth) values(?,?,?,?)";
			pst = conn.prepareStatement(update);
			
			int count=0;
			while(rs.next()){
				String id = rs.getString("logname");
				String labip=rs.getString("labip");
				long durations = rs.getLong("DAY_TIME");
				pst.setString(1, id);
				pst.setString(2, labip);
				pst.setLong(3, durations);
				pst.setDate(4, m);
				pst.addBatch();
				if(++count%10==0){
					pst.executeBatch();
				}
			}
			pst.executeBatch();
			conn.commit();
			
			pst.close();
			rs.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
				throw new CombinateDataException(e1);
			}
			throw new CombinateDataException(e);
		}
	}
//	month的格式"1970-01"
	public void combineMonthData(String month)throws  CombinateDataException{
		try {
			startDB();
			conn.setAutoCommit(false);
			
			String deleteTemp="drop table temp_month";
			PreparedStatement pst = conn.prepareStatement(deleteTemp);
			try {
				pst.execute();//可能temp_month表不存在
			} catch (Exception e) {
			}
			String createTemp="create table temp_month (id char(20), labip char(15) , " +
					"durations numeric(12) , loginmonth Date)";
			pst.execute(createTemp);
			
			String[] arr= month.split("-");
			String month1 = "heantai_day"+arr[0]+arr[1];// 得到日报表的前8位
			for(int i=1;i<=31;i++){
				String insertData = "insert into temp_month (id,labip,durations,loginmonth) select id,labip,durations," +
				"loginmonth from "+month1+String.format("%02d", i);// 得到完整的日报表
				try {
					pst.execute(insertData);
				} catch (Exception e) {}
			}
				
			String queryTempMonth = "select id,labip,sum(durations) month_time" +
			" from temp_month group by id,labip";
			ResultSet rs = pst.executeQuery(queryTempMonth);
			rs.setFetchSize(10);
	
			String createDataMonth = "create table heantai_month"+arr[0]+arr[1]+" (id char(20),labip char(15), " +
					"durations numeric(12) not null, loginyear Date,primary key(id,labip))";
			PreparedStatement create = conn.prepareStatement(createDataMonth);
			create.execute();
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
			Date date = sdf.parse(month);
			String insertMonthData="insert into heantai_month"+arr[0]+arr[1]+" (id,labip,durations,loginyear) " +
					"values(?,?,?,?)";
			PreparedStatement pstinsert=conn.prepareStatement(insertMonthData);
			int count=0;
			while(rs.next()){
				pstinsert.setString(1, rs.getString("id"));
				pstinsert.setString(2, rs.getString("labip"));
				pstinsert.setLong(3, rs.getLong("month_time"));
				java.sql.Date d = new java.sql.Date(date.getTime());
				pstinsert.setDate(4, d);
				pstinsert.addBatch();
				if(++count%10==0){
					pstinsert.executeBatch();
				}
			}
			pstinsert.executeBatch();
			conn.commit();
			
			rs.close();
			pst.close();
			create.close();
			pstinsert.close();
			conn.close();
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
				throw new CombinateDataException(e1);
			}
			e.printStackTrace();
			throw new CombinateDataException(e);
		}
	}
//	year的格式"1970"
	public void combineYearData(String year)throws  CombinateDataException{
		try {
			startDB();
			conn.setAutoCommit(false);
			String deleteTemp="drop table temp_year";
			PreparedStatement pst = conn.prepareStatement(deleteTemp);
			try {
				pst.execute();//表不存在时，删除会出错
			} catch (Exception e) {
//				System.out.println("表不存在");//不能e.printStackTrace();打印
			}
			String createTemp="create table temp_year (id char(20), labip char(15) , " +
					"durations numeric(12) , loginyear Date)";
			pst.execute(createTemp);
			for(int i=1;i<=12;i++){
				String insertData = "insert into temp_year (id,labip,durations,loginyear) select id,labip,durations," +
				"loginyear from heantai_month"+year+String.format("%02d", i);
				try {
					pst.execute(insertData);
				} catch (Exception e) {}
			}
				
			String createDataYear = "create table heantai_year"+year+" (id char(20), labip char(15) , " +
					"durations numeric(12) not null, loginyear Date,primary key(id,labip))";
			pst.execute(createDataYear);
			String queryTempYear = "select id,labip,sum(durations) year_time" +
					" from temp_year group by id,labip";
			ResultSet rs = pst.executeQuery(queryTempYear);
			rs.setFetchSize(10);
			String insertYearData="insert into heantai_year"+year+" (id,labip,durations,loginyear) " +
					"values(?,?,?,?)";
			PreparedStatement pstinsert=conn.prepareStatement(insertYearData);
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
			Date date = sdf.parse(year);
			int count=0;
			while(rs.next()){
				pstinsert.setString(1, rs.getString("id"));
				pstinsert.setString(2, rs.getString("labip"));
				pstinsert.setLong(3, rs.getLong("year_time"));
				java.sql.Date d = new java.sql.Date(date.getTime());
				pstinsert.setDate(4, d);
				pstinsert.addBatch();
				if(++count%10==0){
					pstinsert.executeBatch();
				}
			}
			pstinsert.executeBatch();
			conn.commit();
			rs.close();
			pst.close();
			pstinsert.close();
			conn.close();
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
				throw new CombinateDataException(e1);
			}
			e.printStackTrace();
			throw new CombinateDataException(e);
		}
	}
	public static void main(String[] args) throws Exception {
//		testday();
//		new DataCombinator().combineMonthData("2010-08");
		new DataCombinator().combineYearData("2009");
//		new DataCombinator().dropTables();
		
	}

	
	private void dropTables() throws CombinateDataException, SQLException {
		startDB();
		Statement stm = conn.createStatement();
		String table="day201008";
		for(int i=1;i<=31;i++){
			String sql ="drop table "+table+String.format("%02d", i);
			try {
				stm.execute(sql);
			} catch (Exception e) {}
		}
		stm.close();
		conn.close();
		
	}
	private static void testday() throws CombinateDataException {
		Calendar cal= Calendar.getInstance();
		cal.set(2010, 6, 22,0,0,0);
//		cal.set(2009, 11, 4,0,0,0);
		Calendar end= Calendar.getInstance();
		end.set(2010, 7, 14,0,0,0);
//		end.set(2009, 11, 5,0,0,0);
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		while(cal.before(end)){
			String day=sdf.format(cal.getTime());
			new DataCombinator().combineDayData(day);
			cal.add(Calendar.DAY_OF_MONTH, 1);
		}
	}
}
